Database query languages are computer-readable languages used in querying, updating, and managing relational databases. In database query languages such as standard query language (SQL), objects may depend on tables and other objects stored in the database. For example, an SQL view object may depend on a data table. A traditional database management system (DBMS) supports automatic recompiling whenever objects in the SQL catalog are subject to change (e.g., via alter- or drop-statement) through one of two conventional approaches.
In the first approach, the DBMS provides a “restrict mode” in which any change is rejected if there are other objects depending on the object to be changed. As an example of the foregoing approach, consider the following code snippet:
CREATE TABLE t(x INT, y INT);
CREATE VIEW v AS SELECT y FROM t WHERE y=3;
DROP TABLE t RESTRICT;
In the above example, the DROP statement will fail because VIEW v depends on TABLE t. In this approach, all depending objects must be deleted or changed to remove the dependency. The “RESTRICT” clause automatically drops any dependent object transitively. Because the “restrict mode” offers little flexibility, many DBMSs follow a second approach that supports more graceful data definition statements.
In the second approach, a DBMS provides a “smart mode” that allows changes to objects even if there are dependent objects, and even if the changes result in the invalidation of a dependent object. As an example, consider the following code snippet:
CREATE TABLE t(x INT, y INT);
CREATE VIEW v AS SELECT y FROM t WHERE y=3;
DROP TABLE t;
In the above example, the DROP statement will succeed (i.e., the TABLE t will be dropped), but as a result of dropping t, the VIEW v will become invalid (e.g., VIEW v cannot be recompiled and any usage of VIEW v will result in an error). Thus, the approaches employed under conventional practice either prevent invalidation of dependent objects by preventing all modifications to objects with dependent objects, or allow all modifications to objects, regardless of whether such modification results in the invalidation of a dependent object.
However, situations may arise in which a programmer may want to change objects with dependent objects only when the change does not lead to invalidation of the dependent objects. For example, consider the following code snippet:
CREATE TABLE t(x INT, y INT);
CREATE VIEW v AS SELECT y FROM t WHERE y=3;
ALTER TABLE t DROP COLUMN x;
In the above example, the VIEW v can be successfully reconciled since column x is not used within the definition of v.